package manage;

import entity.FlightInfo;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalTime;
import java.util.ArrayList;

public class FlightDBManager {
    public static ArrayList<FlightInfo> getAllFlights() {
        return getFlight("*","*");
    }

    public static ArrayList<FlightInfo> getFlight(String departurename,String destinationname) {
        ResultSet rs;
        ArrayList<FlightInfo> ret = new ArrayList<FlightInfo>();
        try {
            DBManager dbManager = new DBManager();
            if(departurename.equals("All Cities")&&destinationname.equals("All Cities")){
                rs=dbManager.queryDB("SELECT *FROM FLIGHT_T");
            }
            else if(departurename.equals("All Cities")){
                rs=dbManager.queryDB("SELECT *FROM FLIGHT_T WHERE DESTINATION = '"+destinationname+"'");
            }else if(destinationname.equals("All Cities")){
                rs=dbManager.queryDB("SELECT *FROM FLIGHT_T WHERE DEPARTURE= '"+departurename+"'");
            }else {
                rs=dbManager.queryDB("SELECT *FROM FLIGHT_T WHERE DEPARTURE= '"+departurename+"' AND DESTINATION = '"+destinationname+"'");
            }
            while (rs.next()) {
                String name = rs.getString("NAME");
                Double price = rs.getDouble("PRICE");
                String departure= rs.getString("DEPARTURE");
                String destination= rs.getString("DESTINATION");
                LocalTime departureTime= rs.getTime("DEPARTURETIME").toLocalTime();
                LocalTime arriveTime=rs.getTime("ARRIVETIME").toLocalTime();
                int seat= rs.getInt("seat");
                ret.add(new FlightInfo(name,price,departure,destination,departureTime,arriveTime,seat));
            }
            rs.close();
            dbManager.closeConnections();
        } catch (SQLException ex) {
            System.out.println(ex.getMessage());
        }
        return ret;
    }
    public static void updateFlightSeat(FlightInfo flightInfo, int num) {
        String sql = "";
        if (num >= 0) {
            sql = "UPDATE FLIGHT_T SET SEAT=SEAT + " + num + " WHERE NAME = '" + flightInfo.getName() + "'";
        } else {
            sql = "UPDATE FLIGHT_T SET SEAT=SEAT " + num + " WHERE NAME = '" + flightInfo.getName() + "'";
        }
        DBManager dbManager = new DBManager();
        dbManager.establishConnection();
        dbManager.updateDB(sql);
        dbManager.closeConnections();
    }
}
